﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ES_Project.Model;
using System.Data;
using System.Data.SqlClient;

using System.Collections;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;

using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
namespace ES_Project.Pages
{
    public partial class WebForm16 : System.Web.UI.Page
    {
        string StrConn = WebConfigurationManager.ConnectionStrings["baseessystem"].ToString();
        MySqlCommand objCmd = new MySqlCommand(); 
        protected void Page_Load(object sender, EventArgs e)
        {
            
               
        }
        void datastudent()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);

            string sqlsm;


            sqlsm = "select * from student_mast where st_code = '" + TextBox3.Text + "' ";


            Conn.Open();
            MySqlDataAdapter da = new MySqlDataAdapter(sqlsm, Conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            int _row = ds.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                Label2.Text = (string)ds.Tables[0].Rows[i]["st_name"].ToString();


            }
            else
            {
                Label2.Text = "ไม่พบรหัสนักเรียน";
            }
        }
       
        void datafamily()
        {
            MySqlConnection Conn = new MySqlConnection(StrConn);

            string sqlsm;


            sqlsm = "select * from student_mast where family_code = '" + TextBox1.Text + "' ";


            Conn.Open();
            MySqlDataAdapter da = new MySqlDataAdapter(sqlsm, Conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            int _row = ds.Tables[0].Rows.Count;
            int i = 0;
            if (_row > i)
            {

                Label1.Text = (string)ds.Tables[0].Rows[i]["family_name"].ToString();


            }
            else
            {
                Label1.Text = "ไม่พบรหัสครอบครัว";
            }


         

        }
      
        protected void Button1_Click(object sender, EventArgs e)
        {
            datafamily();
            datastudent();

            string condi = "where 1=1";
            if (!TextBox1.Text.Equals(""))
            {

                condi = condi + " and (  family_code ='" + TextBox1.Text + "')";
            }
            if (!TextBox3.Text.Equals(""))
            {

                condi = condi + " and (  st_code ='" + TextBox3.Text + "')";

            }

            if (TextBox4.Text.Trim() != "" && TextBox5.Text.Trim() != "")
            {
                condi = condi + " and (  register_date between '" + Convert.ToDateTime(TextBox4.Text).AddYears(-543).ToString("yyyy-MM-dd") + "' and '" + Convert.ToDateTime(TextBox5.Text).AddYears(-543).ToString("yyyy-MM-dd") + "')";
            }
            MySqlConnection Conn = new MySqlConnection(StrConn);

            string sqlsm;


            sqlsm = "select * from student_mast " + condi;

            //sqlsm = "SELECT * FROM Student_Mast inner join student_detail on Student_Mast.st_id = student_detail.st_id "+ condi;
            Conn.Open();
            MySqlDataAdapter da = new MySqlDataAdapter(sqlsm, Conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "Student_Mast");
            GridView1.DataSource = ds.Tables["Student_Mast"];
            GridView1.DataBind();


            if (ds.Tables[0].Rows.Count == 0)
            {
                GridView1.DataSource = null;
                GridView1.DataBind();



                ScriptManager.RegisterClientScriptBlock(this, typeof(Page), "ReturnMessinit1", "alert('ไม่พบข้อมูล');", true);
            }


           
        }

        protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            HyperLink family_code = (HyperLink)(e.Row.FindControl("family_code"));
            if (family_code != null)
            {
                Session["passrun"] = "";
                family_code.NavigateUrl = "FamilySearchData.aspx?family_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_code")) + "&register_date=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "register_date")) + "&family_name=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_name")) + "&st_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "st_code"));
                
            }
            HyperLink family_name = (HyperLink)(e.Row.FindControl("family_name"));
            if (family_name != null)
            {
                Session["passrun"] = "";
                family_name.NavigateUrl = "FamilySearchData.aspx?family_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_code")) + "&register_date=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "register_date")) + "&family_name=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_name")) + "&st_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "st_code"));

            }
            HyperLink st_code = (HyperLink)(e.Row.FindControl("st_code"));
            if (st_code != null)
            {
                Session["passrun"] = "";
                st_code.NavigateUrl = "FamilySearchData.aspx?family_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_code")) + "&register_date=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "register_date")) + "&family_name=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_name")) + "&st_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "st_code"));

            }
            HyperLink st_name = (HyperLink)(e.Row.FindControl("st_name"));
            if (st_name != null)
            {
                Session["passrun"] = "";
                st_name.NavigateUrl = "FamilySearchData.aspx?family_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_code")) + "&register_date=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "register_date")) + "&family_name=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_name")) + "&st_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "st_code"));

            }
            HyperLink register_date = (HyperLink)(e.Row.FindControl("register_date"));
            if (register_date != null)
            {
                Session["passrun"] = "";
                register_date.NavigateUrl = "FamilySearchData.aspx?family_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_code")) + "&register_date=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "register_date")) + "&family_name=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "family_name")) + "&st_code=" + Convert.ToString(DataBinder.Eval(e.Row.DataItem, "st_code"));

            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            TextBox1.Text = "";
            TextBox3.Text = "";
            TextBox4.Text = "";
            TextBox5.Text = "";
            Label1.Text = "";
            Label2.Text = "";
        }

       

       

   
       

    }
}